/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import util.IdbUtil;
import util.StringUtil;
import util.Utils;

/**
 *
 * @author Thinh
 */
public class UserDAO extends BaseDAO<User> {

    private Object ResultSet;

    public UserDAO(IdbUtil _database) {
        super(_database);
    }

    @Override
    public Collection<User> getAll() throws SQLException {
        /** Declare variables to connect to DB**/
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<User> users = new ArrayList<User>();
        /** End declare variables **/
        try {
            String queryString = "SELECT * FROM [dbo].[User]";
            connection = _database.getConnection();
            statement = connection.prepareStatement(queryString);
            resultSet = statement.executeQuery();

            User tempUser;

            while (resultSet.next()) {
                tempUser = new User();
                tempUser = mappingEntity(tempUser, resultSet);
                users.add(tempUser);
            }

        } catch (SQLException ex) {
            throw ex;
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                throw ex;
            }
        }
        return users;
    }

    @Override
    public User get(int id) throws SQLException {
        /** Declare variables to connect to DB**/
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        User user = new User();
        /** End declare variables **/
        try {
            String queryString = "SELECT * FROM User WHERE id = ?";
            connection = _database.getConnection();
            statement = connection.prepareStatement(queryString);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();

            while (resultSet.next()) {
                user = mappingEntity(user, resultSet);
            }
        } catch (SQLException ex) {
            throw ex;
        }finally {
            try {
                if (resultSet != null)
                {
                   resultSet.close();
                }
                if (statement != null)
                {
                   statement.close();
                }
                if (connection != null)
                {
                   connection.close();
                }
            } catch (SQLException ex) {
             throw ex;
            }
        }
        return user;
    }

    @Override
    public User create(User entity) throws SQLException {
         /** Declare variables to connect to DB**/
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            String queryString = "INSERT INTO [dbo].[User] VALUES(?,?,?,?,?,?)";
            connection = _database.getConnection();
            
            statement = connection.prepareStatement(queryString);
            statement.setString(1, entity.getUsername());
            statement.setString(2, entity.getPassword());
            statement.setBoolean(3, entity.isIs_admin());
            statement.setDate(4, Utils.convertToSqlDate(entity.getCreate_date()));
            statement.setDate(5, Utils.convertToSqlDate(entity.getUpdate_date()));
            statement.setBoolean(6, entity.isIs_deleted());

            statement.execute();
        }catch (SQLException ex) {
            throw ex;
        }finally {
            try {
                if (resultSet != null)
                {
                   resultSet.close();
                }
                if (statement != null)
                {
                   statement.close();
                }
                if (connection != null)
                {
                   connection.close();
                }
            } catch (SQLException ex) {
             throw ex;
            }
        }
        return entity;
    }

    @Override
    public void update(User entity) throws SQLException {
          /** Declare variables to connect to DB**/
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        /** End declare variables **/
        try {
            StringBuilder queryString = new StringBuilder();
            queryString.append("UPDATE [dbo].[User] SET");
            queryString.append(" [username] = ?");
            queryString.append(",[password] = ?");
            queryString.append(",[is_admin] = ?");
            queryString.append(",[create_date] = ?");
            queryString.append(",[update_date] = ?");
            queryString.append(",[is_deleted] = ?");
            queryString.append(" WHERE [dbo].[User].id = ?");
            connection = _database.getConnection();
            statement = connection.prepareStatement(queryString.toString());

            statement.setString(1, entity.getUsername());
            statement.setString(2, entity.getPassword());
            statement.setBoolean(3, entity.isIs_admin());
            statement.setDate(4, Utils.convertToSqlDate(entity.getCreate_date()));
            statement.setDate(5, Utils.convertToSqlDate(entity.getUpdate_date()));
            statement.setBoolean(6, entity.isIs_deleted());
            statement.setInt(7, entity.getId());

            statement.execute();
        }catch(SQLException ex){
            throw ex;
        }finally {
            try {
                if (resultSet != null)
                {
                   resultSet.close();
                }
                if (statement != null)
                {
                   statement.close();
                }
                if (connection != null)
                {
                   connection.close();
                }
            } catch (SQLException ex) {
              throw ex;
            }
        }
    }

    @Override
    public boolean delete(int id) throws SQLException {
        boolean result = false;
       /** Declare variables to connect to DB**/
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
       /** End declare variables **/
        try {
            String queryString = "DELETE FROM [dbo].[User] WHERE [dbo].[User].id = ?";

            connection = _database.getConnection();
            statement = connection.prepareStatement(queryString.toString());

            statement.setInt(1, id);

            result = statement.execute();
        } catch (SQLException ex) {
            throw ex;
        }
        return result;
    }

    /**
     * Mapping result from database to entity
     * @param tempUser Entity
     * @param resultSet ResultSet from database
     * @return Entity
     * @throws SQLException
     */
    private User mappingEntity(User tempUser, ResultSet resultSet) throws SQLException {
       
           tempUser.setId(resultSet.getInt(StringUtil.Id));
        
        tempUser.setUsername(resultSet.getString(StringUtil.Username));
        tempUser.setPassword(resultSet.getString(StringUtil.Password));
        tempUser.setCreate_date(resultSet.getDate(StringUtil.CreatedDate));
        tempUser.setUpdate_date(resultSet.getDate(StringUtil.UpdatedDate));
        tempUser.setIs_deleted(resultSet.getBoolean(StringUtil.IsDeleted));
        tempUser.setIs_admin(resultSet.getBoolean(StringUtil.Is_Admin));

        return tempUser;
    }
}
